package com.liubujun;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.liubujun.entity.*;
import com.liubujun.proxy.zhongjianshang.TaoBao;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;
import org.springframework.beans.BeanUtils;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Author: liubujun
 * @Date: 2021/11/7 16:10
 */


public class TestPoi {

    /**
     * 模拟数据库中的数据
     * @return
     */
    public List<User> getUsers(){
        List<User> users = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            User user = new User();
            user.setId(i);
            user.setName("小王"+i);
            user.setAge(i);
            user.setBir(new Date());
            if(i%2 == 0){
                user.setStatus("1");
            }else {
                user.setStatus("0");
            }
            users.add(user);
        }
        return users;
    }

    /**
     * 将数据库中的数据导出
     * @throws Exception
     */
    @Test
    public void testExport() throws Exception{
        //获取数据
        List<User> users = getUsers();
        //导出excel
        //参数1:ExportParams导出配置对象 参数2:导出的类型 参数3：导出的数据集合
        Workbook workbook  = ExcelExportUtil.exportExcel(new ExportParams("用户信息列表", "用户信息"), User.class, users);
        //将excel写入指定位置
        FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\LiuBuJun\\Desktop\\预约信息表格\\aa.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        workbook.close();
    }

    /**
     * 将表格中的数据读取出来
     * @throws Exception
     */
    @Test
    public void testImport1() throws Exception{
        ImportParams params = new ImportParams();
        params.setTitleRows(1);//标题占几行 告诉熊哪行开始读取
        params.setHeadRows(1);//header占几行
        //params.setStartSheetIndex(2);从第一个sheet开始读物，默认是1
        List<Emp> emps = ExcelImportUtil.importExcel(new FileInputStream("C:\\Users\\LiuBuJun\\Desktop\\预约信息表格\\aa.xls"), Emp.class, params);
        String collect = emps.stream().map(emp -> "\'" + emp.getAge() + "\'").collect(Collectors.joining(","));
        System.out.println(collect);
    }

    @Test
    public void testImport9() throws Exception{
        ImportParams params = new ImportParams();
        params.setHeadRows(1);//header占几行
        //params.setStartSheetIndex(2);从第一个sheet开始读物，默认是1
        List<HB> hbs = ExcelImportUtil.importExcel(new FileInputStream("C:\\Users\\LiuBuJun\\Desktop\\日期\\月份.xlsx"), HB.class, params);
        String collect = hbs.stream().map(emp -> "\'" + emp.getId() + "\'").collect(Collectors.joining(","));
        System.out.println(collect);
    }

    @Test
    public void testImport2() throws Exception{
        ImportParams params = new ImportParams();
//        params.setTitleRows(1);//标题占几行 告诉熊哪行开始读取
        params.setHeadRows(1);//header占几行
        //params.setStartSheetIndex(2);从第一个sheet开始读物，默认是1  //update ent_group_order set hide_phone = NULL where id = 10289
        List<Hospital> hospitals = ExcelImportUtil.importExcel(new FileInputStream("C:\\Users\\LiuBuJun\\Desktop\\预约信息表格\\美年新增医院列表11.23.xlsx"), Hospital.class, params);
        hospitals.forEach(hospital -> {
            System.out.println("update hsp_hospital set open_type = 5,open_code = '" +hospital.getMeiNianCode() + "'" + " where code = '" + hospital.getCode()+"';" );
        });
//        String collect = emps.stream().map(emp -> "\'" + emp.getAge() + "\'").collect(Collectors.joining(","));

    }

    @Test
    public void testImport4() throws Exception{
        ImportParams params = new ImportParams();
//        params.setTitleRows(1);//标题占几行 告诉熊哪行开始读取
        params.setHeadRows(1);//header占几行
        //params.setStartSheetIndex(2);从第一个sheet开始读物，默认是1  //update ent_group_order set hide_phone = NULL where id = 10289
        List<HS> hospitals = ExcelImportUtil.importExcel(new FileInputStream("C:\\Users\\LiuBuJun\\Desktop\\日期\\该字段表.xlsx"), HS.class, params);
        List list = new ArrayList();
        hospitals.forEach(hospital -> {
            list.add(hospital.getId());
        });
        System.out.println(list);

    }

    @Test
    public void testImport6() throws Exception{
        ImportParams params = new ImportParams();
//        params.setTitleRows(1);//标题占几行 告诉熊哪行开始读取
        params.setHeadRows(1);//header占几行
        //params.setStartSheetIndex(2);从第一个sheet开始读物，默认是1  //update ent_group_order set hide_phone = NULL where id = 10289
        List<HC> hospitals = ExcelImportUtil.importExcel(new FileInputStream("C:\\Users\\LiuBuJun\\Desktop\\日期\\渠道.xlsx"), HC.class, params);
        hospitals.forEach(hospital -> {
//            update hsp_group_checkup_batch set rent_id = 1426532613791637505 where source_channel_code = 'CH18019000'
            System.out.println("update hsp_group_checkup_batch set rent_id = "+hospital.getCode() + " where source_channel_code = "+hospital.getId());
        });


    }

    @Test
    public void testImport3() throws Exception{
        ImportParams params = new ImportParams();
//        params.setTitleRows(1);//标题占几行 告诉熊哪行开始读取
        params.setHeadRows(1);//header占几行
        //params.setStartSheetIndex(2);从第一个sheet开始读物，默认是1
        List<Hospital> hospitals = ExcelImportUtil.importExcel(new FileInputStream("C:\\Users\\LiuBuJun\\Desktop\\预约信息表格\\美年新增医院列表11.23.xlsx"), Hospital.class, params);
        String collect = hospitals.stream().map(hospital -> "\'" + hospital.getCode() + "\'").collect(Collectors.joining(","));
        System.out.println(collect);
    }

    @Test
    public void test5(){
       String str = "'YY21328270980578','YY21220990880548','YY21729235210255','YY21803185830729','YY21420306060403','YY21216122890546','YY21653935550578','YY21458926670348','YY21508415480495','YY21889760270269','YY21348371620828','YY21976527811001','YY21909380230685','YY21634703390385','YY21545132690068','YY21282336760984','YY21732170860994','YY21847495880927','YY21548488100279','YY21550965320803','YY21214467370408','YY21309380851001','YY21324347200935','YY21925286260534','YY21564679390151','YY21529229720958','YY21994394860093','YY21158718070651','YY21942826850911','YY21714529030308','YY21627670160869','YY21883176571002','YY21121088780385','YY21332617930125','YY21844690720478','YY21797515340458','YY21668716820940','YY21803582340782','YY21108042730742','YY21532906890724','YY21220683200437','YY21279176700926','YY21599613050273','YY21556145020428','YY21527545601019','YY21116024580291','YY21214036280153','YY21954941920718','YY21981600950574','YY21516617670492','YY21828297120314','YY21791479280800','YY21344158090201','YY21608288950095','YY21434436710651','YY21721262390635','YY21654561380979','YY21441857710519','YY21432592480249','YY21317353650296','YY21764424860175','YY21201223570452','YY21707846510510','YY21923484190052'";
        String[] split = str.split(",");
        System.out.println(Arrays.toString(split));
        List<String> list = Arrays.asList(split);
        list.forEach(o->{
            System.out.println("update hsp_hospital set open_type = 5 where code =" +o+";");
        });

    }

    @Test
    public void testImport7() throws Exception{
        ImportParams params = new ImportParams();
//        params.setTitleRows(1);//标题占几行 告诉熊哪行开始读取
        params.setHeadRows(1);//header占几行
        //params.setStartSheetIndex(2);从第一个sheet开始读物，默认是1
        List<C> hospitals = ExcelImportUtil.importExcel(new FileInputStream("C:\\Users\\LiuBuJun\\Desktop\\导入导出\\医院id.xlsx"), C.class, params);
        String collect = hospitals.stream().map(hospital -> "\'" + hospital.getId() + "\'").collect(Collectors.joining(","));
        System.out.println(collect);
    }




    public static void testByString(Integer count)  {
        long startTime = System.currentTimeMillis();
        String initStr = "abc";
        for (int i = 0; i < count; i++) {
            initStr = initStr + i;
        }
        long endTime = System.currentTimeMillis();
        System.out.println("String拼接字符串共消耗"+(endTime-startTime)+"ms");
    }



    public static void testByStringBuilder(Integer count)  {
        long startTime = System.currentTimeMillis();
        StringBuilder stringBuilder = new StringBuilder();
        for (int i = 0; i < count; i++) {
            stringBuilder.append(i);
        }
        long endTime = System.currentTimeMillis();
        System.out.println("StringBuilder拼接字符串共消耗"+(endTime-startTime)+"ms");
    }


    public static void testByStringBuffer(Integer count)  {
        long startTime = System.currentTimeMillis();
        StringBuffer stringBuffer = new StringBuffer();
        for (int i = 0; i < count; i++) {
            stringBuffer.append(i);
        }
        long endTime = System.currentTimeMillis();
        System.out.println("StringBuffer拼接字符串共消耗"+(endTime-startTime)+"ms");
    }

    public static void main(String[] args) {
        A a1 = new A(1,"张三",12);
        B b = new B();

        BeanUtils.copyProperties(a1,b);

        System.out.println(b);


    }

    //获取一段时间内的所有日期
    private  List<String> findDates(String dBegin, String dEnd) throws ParseException {
        DateFormat format = new SimpleDateFormat("yyyy-MM-dd");

        //设置开始时间
        Calendar calBegin = Calendar.getInstance();
        calBegin.setTime(format.parse(dBegin));
        //设置结束时间
//        Calendar calEnd = Calendar.getInstance();
//        calEnd.setTime(format.parse(dEnd));

        List<String> Datelist = new ArrayList<>();
        //加上开始时间
        Datelist.add(format.format(calBegin.getTime()));
        // 每次循环给calBegin日期加一天，直到calBegin.getTime()时间等于dEnd
        while (format.parse(dEnd).after(calBegin.getTime()))  {
            // 根据日历的规则，为给定的日历字段添加或减去指定的时间量
            calBegin.add(Calendar.DAY_OF_MONTH, 1);
            Datelist.add(format.format(calBegin.getTime()));
        }
        System.out.println(Datelist.size());

        return Datelist;
    }

    @Test
    public void testArrays(){
        String txNotice = "体检时间：可预约日期上午，理想的抽血时间是早上8:00～10:00。&标识符&体检凭证：请您确认预约体检的时间，于体检日早上携带身份证，到体检中心前台登记并领取导检单。&标识符&体检报告：当您完成全部体检项目后，体检报告具体领取方式咨询体检中心前台。&标识符&发票须知：由势成一健康开具电子发票，申请发票后3个工作日内开具到指定邮箱。&标识符&订单退改：未体检订单支持随时退款，退款不扣除额外费用；如需修改体检时间请至少在原体检日前1天发起申请。&标识符&注意事项：体检前3天请您清淡饮食，体检前晚10点以后禁饮禁食，体检日早上请保持空腹；慢性疾病患者请将平时服用的药物携带备用，受检日建议不要停药。怀孕或准备怀孕的女性不要做X射线检查；婴幼儿和少年儿童不安排X线放射检查。&标识符&若有其他疑问，请在工作日9:30～17:00拨打势成一健康客服热线：400-188-9871。&标识符&";
        StringBuffer buffer = new StringBuffer();
        String[] split = txNotice.split("&标识符&");
        for (int i = 0; i < split.length; i++) {
            Integer index = i+1;
            String str = index+".";
            String s = split[i];
            String ss = str+s;
            buffer.append("\n").append(ss);
        }
        List<String> notices = Arrays.asList(buffer.toString());
        System.out.println(notices);
    }

}
